﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CSP.Dao.IObjectDaos.CSPBBS;
using CSP.Model.CSPBBS;
using MySql.Data.MySqlClient;
using CSP.DataAccess;
using System.Data;
using System.Collections.ObjectModel;
using CSP.Common.Tools;
using CSP.Dao.CommonDao;

namespace CSP.Dao.ObjectDaos.CSPBBS
{
    public class MemberProfileDao : CSPBBSCommonDao, IMemberProfileDao
    {
        private const string TABLE = "csp_common_member_profile";

        public ICollection<MemberProfile> FindAll()
        {
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();
            DataSet ds = null;
            try
            {
                ds = MySqlHelperFactory.ExecuteDataset(conn, CommandType.Text, "select * from " + TABLE);
                //MySqlDataReader reader1 = MySqlHelperFactory.ExecuteReader(conn, CommandType.Text, "select * from " + TABLE);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return DataSetToMembers(ds);
        }

        public MemberProfile GetDataById(int id)
        {
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();
            MySqlDataReader reader = null;

            MemberProfile result = null;
            try
            {
                reader = MySqlHelperFactory.ExecuteReader(conn, CommandType.Text, "select * from " + TABLE + " where uid =" + id);
                ICollection<MemberProfile> memberMemberProfiles = ReaderToMemberProfiles(reader);
                result = memberMemberProfiles.ElementAtOrDefault(0);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                conn.Close();
            }
            return result;
        }

        public ICollection<MemberProfile> GeDatatByIds(int[] Ids)
        {
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();
            MySqlDataReader reader = null;

            ICollection<MemberProfile> result;
            try
            {
                reader = MySqlHelperFactory.ExecuteReader(conn, CommandType.Text, "select * from " + TABLE + " where uid in (" + string.Join(",", Ids) + ")");
                result = ReaderToMemberProfiles(reader);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                conn.Close();
            }

            return result;
        }

        public void Add(MemberProfile obj)
        {
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();

            string insertSql = "insert into " + TABLE + " (uid,realname,gender,birthyear,birthmonth,birthday,constellation,zodiac,telephone,mobile,idcardtype,idcard,address,zipcode,nationality,birthprovince,birthcity,birthdist,birthcommunity,resideprovince,residecity,residedist,residecommunity,residesuite,graduateschool,company,education,occupation,position,revenue,affectivestatus,lookingfor,bloodtype,height,weight,alipay,icq,qq,yahoo,msn,taobao,site,bio,interest) values";
            insertSql += "(@uid,@realname,@gender,@birthyear,@birthmonth,@birthday,@constellation,@zodiac,@telephone,@mobile,@idcardtype,@idcard,@address,@zipcode,@nationality,@birthprovince,@birthcity,@birthdist,@birthcommunity,@resideprovince,@residecity,@residedist,@residecommunity,@residesuite,@graduateschool,@company,@education,@occupation,@position,@revenue,@affectivestatus,@lookingfor,@bloodtype,@height,@weight,@alipay,@icq,@qq,@yahoo,@msn,@taobao,@site,@bio,@interest)";
            MySqlParameter[] parms = GetParmsForAddOrUpdate(obj);
            try
            {
                int result = MySqlHelperFactory.ExecuteNonQuery(conn, CommandType.Text, insertSql, parms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public void Update(MemberProfile obj)
        {
            MySqlConnection conn = MySqlHelperFactory.GetBBSConnection();

            string updateSql = "update " + TABLE + "  set realname=@realname,gender=@gender,birthyear=@birthyear,birthmonth=@birthmonth,birthday=@birthday,constellation=@constellation,zodiac=@zodiac,telephone=@telephone,mobile=@mobile,idcardtype=@idcardtype,idcard=@idcard,address=@address,zipcode=@zipcode,nationality=@nationality,birthprovince=@birthprovince,birthcity=@birthcity,birthdist=@birthdist,birthcommunity=@birthcommunity,resideprovince=@resideprovince,residecity=@residecity,residedist=@residedist,residecommunity=@residecommunity,residesuite=@residesuite,graduateschool=@graduateschool,company=@company,education=@education,occupation=@occupation,position=@position,revenue=@revenue,affectivestatus=@affectivestatus,lookingfor=@lookingfor,bloodtype=@bloodtype,height=@height,weight=@weight,alipay=@alipay,icq=@icq,qq=@qq,yahoo=@yahoo,msn=@msn,taobao=@taobao,site=@site,bio=@bio,interest=@interest where uid=@uid";

            MySqlParameter[] parms = GetParmsForAddOrUpdate(obj);
            try
            {
                int result = MySqlHelperFactory.ExecuteNonQuery(conn, CommandType.Text, updateSql, parms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public MemberProfile DeleteById(int id)
        {
            throw new NotImplementedException();
        }

       

        private ICollection<MemberProfile> DataSetToMembers(DataSet ds)
        {
            ICollection<MemberProfile> result = new Collection<MemberProfile>();
            if (ds.Tables.Count == 0)
            {
                return result;
            }
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                MemberProfile memberProfile = ObjsToMember<DataRow>(ds.Tables[0].Rows[i]);
                result.Add(memberProfile);
            }
            return result;
        }

        private ICollection<MemberProfile> ReaderToMemberProfiles(MySqlDataReader reader)
        {
            ICollection<MemberProfile> result = new Collection<MemberProfile>();
            int i = 0;
            do
            {

                while (reader.Read())
                {
                    MemberProfile memberProfile = ObjsToMember<MySqlDataReader>(reader);
                    result.Add(memberProfile);
                }
                i++;
            } while (reader.NextResult()); //NextResult()移动到下一个记录集 

            return result;
        }

        /// <summary>
        /// 将数组数据做对象对配转换
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objs"></param>
        /// <returns></returns>
        private MemberProfile ObjsToMember<T>(T objs)
        {
            if (objs == null)
            {
                return null;
            }
            //获取当前实例类型
            Type type = objs.GetType();
            //获取实例索引器
            var property = type.GetProperty("Item", new Type[] { typeof(string) });

            //创建返回值实例
            MemberProfile result = new MemberProfile();

            result.Uid = Convert.ToInt32(property.GetValue(objs, new object[] { "uid" }));
            result.Realname = property.GetValue(objs, new object[] { "realname" }).ToString();
            result.Gender = Convert.ToInt32(property.GetValue(objs, new object[] { "gender" }));
            result.Birthyear = Convert.ToInt32(property.GetValue(objs, new object[] { "birthyear" }));
            result.Birthmonth = Convert.ToInt32(property.GetValue(objs, new object[] { "birthmonth" }));
            result.Birthday = CommonTool.ConvertToDateTime(Convert.ToInt32(property.GetValue(objs, new object[] { "birthday" })));
            result.Constellation = property.GetValue(objs, new object[] { "constellation" }).ToString();
            result.Zodiac = property.GetValue(objs, new object[] { "zodiac" }).ToString();
            result.Telephone = property.GetValue(objs, new object[] { "telephone" }).ToString();
            result.Mobile = property.GetValue(objs, new object[] { "mobile" }).ToString();
            result.Idcardtype = property.GetValue(objs, new object[] { "idcardtype" }).ToString();
            result.Idcard = property.GetValue(objs, new object[] { "idcard" }).ToString();
            result.Address = property.GetValue(objs, new object[] { "address" }).ToString();
            result.Zipcode = property.GetValue(objs, new object[] { "zipcode" }).ToString();
            result.Nationality = property.GetValue(objs, new object[] { "nationality" }).ToString();
            result.Birthprovince = property.GetValue(objs, new object[] { "birthprovince" }).ToString();
            result.Birthcity = property.GetValue(objs, new object[] { "birthcity" }).ToString();
            result.Birthdist = property.GetValue(objs, new object[] { "birthdist" }).ToString();
            result.Birthcommunity = property.GetValue(objs, new object[] { "birthcommunity" }).ToString();
            result.Resideprovince = property.GetValue(objs, new object[] { "resideprovince" }).ToString();
            result.Residecity = property.GetValue(objs, new object[] { "residecity" }).ToString();
            result.Residedist = property.GetValue(objs, new object[] { "residedist" }).ToString();
            result.Residecommunity = property.GetValue(objs, new object[] { "residecommunity" }).ToString();
            result.Residesuite = property.GetValue(objs, new object[] { "residesuite" }).ToString();
            result.Graduateschool = property.GetValue(objs, new object[] { "graduateschool" }).ToString();
            result.Company = property.GetValue(objs, new object[] { "company" }).ToString();
            result.Education = property.GetValue(objs, new object[] { "education" }).ToString();
            result.Occupation = property.GetValue(objs, new object[] { "occupation" }).ToString();
            result.Position = property.GetValue(objs, new object[] { "position" }).ToString();
            result.Revenue = property.GetValue(objs, new object[] { "revenue" }).ToString();
            result.Affectivestatus = property.GetValue(objs, new object[] { "affectivestatus" }).ToString();
            result.Lookingfor = property.GetValue(objs, new object[] { "lookingfor" }).ToString();
            result.Bloodtype = property.GetValue(objs, new object[] { "bloodtype" }).ToString();
            result.Height = property.GetValue(objs, new object[] { "height" }).ToString();
            result.Weight = property.GetValue(objs, new object[] { "weight" }).ToString();
            result.Alipay = property.GetValue(objs, new object[] { "alipay" }).ToString();
            result.Icq = property.GetValue(objs, new object[] { "icq" }).ToString();
            result.QQ = property.GetValue(objs, new object[] { "qq" }).ToString();
            result.Yahoo = property.GetValue(objs, new object[] { "yahoo" }).ToString();
            result.Msn = property.GetValue(objs, new object[] { "msn" }).ToString();
            result.TaoBao = property.GetValue(objs, new object[] { "taobao" }).ToString();
            result.Site = property.GetValue(objs, new object[] { "site" }).ToString();
            result.Bio = property.GetValue(objs, new object[] { "bio" }).ToString();
            result.Interest = property.GetValue(objs, new object[] { "interest" }).ToString();


            return result;
        }

        private MySqlParameter[] GetParmsForAddOrUpdate(MemberProfile memberProfile)
        {
            MySqlParameter[] parms = new MySqlParameter[] {
                //GetParameter("uid",DbType.Int32,8,member.Id),
            
            GetParameter("@uid",DbType.Int32,8,memberProfile.Uid),
            GetParameter("@realname",DbType.String,255,memberProfile.Realname),
            GetParameter("@gender",DbType.Int32,1,memberProfile.Gender),
            GetParameter("@birthyear",DbType.Int32,6,memberProfile.Birthyear),
            GetParameter("@birthmonth",DbType.Int32,6,memberProfile.Birthmonth),
            GetParameter("@birthday",DbType.Int32,6,CommonTool.ConvertToTimeSpan( memberProfile.Birthday)),
            GetParameter("@constellation",DbType.String,255,memberProfile.Constellation),
            GetParameter("@zodiac",DbType.String,255,memberProfile.Zodiac),
            GetParameter("@telephone",DbType.String,255,memberProfile.Telephone),
            GetParameter("@mobile",DbType.String,255,memberProfile.Mobile),
            GetParameter("@idcardtype",DbType.String,255,memberProfile.Idcardtype),
            GetParameter("@idcard",DbType.String,255,memberProfile.Idcard),
            GetParameter("@address",DbType.String,255,memberProfile.Address),
            GetParameter("@zipcode",DbType.String,255,memberProfile.Zipcode),
            GetParameter("@nationality",DbType.String,255,memberProfile.Nationality),
            GetParameter("@birthprovince",DbType.String,255,memberProfile.Birthprovince),
            GetParameter("@birthcity",DbType.String,255,memberProfile.Birthcity),
            GetParameter("@birthdist",DbType.String,20,memberProfile.Birthdist),
            GetParameter("@birthcommunity",DbType.String,255,memberProfile.Birthcommunity),
            GetParameter("@resideprovince",DbType.String,255,memberProfile.Birthcommunity),
            GetParameter("@residecity",DbType.String,255,memberProfile.Residecity),
            GetParameter("@residedist",DbType.String,20,memberProfile.Residedist),
            GetParameter("@residecommunity",DbType.String,20,memberProfile.Residecommunity),
            GetParameter("@residesuite",DbType.String,255,memberProfile.Residesuite),
            GetParameter("@graduateschool",DbType.String,255,memberProfile.Graduateschool),
            GetParameter("@company",DbType.String,255,memberProfile.Company),
            GetParameter("@education",DbType.String,255,memberProfile.Education),
            GetParameter("@occupation",DbType.String,255,memberProfile.Occupation),
            GetParameter("@position",DbType.String,255,memberProfile.Position),
            GetParameter("@revenue",DbType.String,255,memberProfile.Revenue),
            GetParameter("@affectivestatus",DbType.String,255,memberProfile.Affectivestatus),
            GetParameter("@affectivestatus",DbType.String,255,memberProfile.Affectivestatus),
            GetParameter("@bloodtype",DbType.String,255,memberProfile.Bloodtype),
            GetParameter("@height",DbType.String,255,memberProfile.Height),
            GetParameter("@weight",DbType.String,255,memberProfile.Weight),
            GetParameter("@alipay",DbType.String,255,memberProfile.Alipay),
            GetParameter("@icq",DbType.String,255,memberProfile.Icq),
            GetParameter("@qq",DbType.String,255,memberProfile.QQ),
            GetParameter("@yahoo",DbType.String,255,memberProfile.Yahoo),
            GetParameter("@msn",DbType.String,255,memberProfile.Msn),
            GetParameter("@taobao",DbType.String,255,memberProfile.TaoBao),
            GetParameter("@site",DbType.String,255,memberProfile.Site),
            GetParameter("@bio",DbType.String,20,memberProfile.Bio),
            GetParameter("@interest",DbType.String,20,memberProfile.Interest)
            
            };

            return parms;
        }
    }
}
